﻿CREATE VIEW [dbo].[OEMSailsView]
AS
SELECT     OEMSailListID, BoatGenID, OEMIdentifier, CustomerIdentifier, CustomerID, MarketID, QuoteSailTypeID, Area, GenericYarnID, FilmTypeID, DPI, 
                      CurrencyID, TierID, UsageID, Createdate, ConstructionID, MarketRetailPrice, ListRetailPrice, ROUND(OEMRetailPrice, 0) AS OEMRetailPRice, Cut, 
                      Weight, AccessoryID, SailNumberColourID, AccessoryColourID, NylonColourDetails, FabricID, NotionalArea, TopSurfaceID, BotSurfaceID, 
                      GenSailTypeID, Notes, OptionID, DesignFile, Standard, QuoteLineItemID, QuoteID, LineItem, CASE WHEN AxaptaCode IS NOT NULL 
                      THEN AxaptaCode ELSE '0' END AS AxaptaCode,
                          (SELECT     SailType
                            FROM          dbo.QuoteSailType_Tbl
                            WHERE      (QuoteSailTypeID = dbo.OEMSailList_Tbl.QuoteSailTypeID)) AS SailType,
                          (SELECT     YarnType
                            FROM          dbo.GenericYarn_Tbl
                            WHERE      (GenericYarnID = dbo.OEMSailList_Tbl.GenericYarnID)) AS Yarntype,
                          (SELECT     FilmType
                            FROM          dbo.FilmType_Tbl
                            WHERE      (FilmTypeID = dbo.OEMSailList_Tbl.FilmTypeID)) AS FilmType,
                          (SELECT     Tier
                            FROM          dbo.Tier_Tbl
                            WHERE      (TierID = dbo.OEMSailList_Tbl.TierID)) AS Tier,
                          (SELECT     TierPriceIndicator
                            FROM          dbo.Tier_Tbl AS Tier_Tbl_1
                            WHERE      (TierID = dbo.OEMSailList_Tbl.TierID)) AS TierPriceIndicator,
                          (SELECT     UsageType
                            FROM          dbo.Usage_Tbl
                            WHERE      (UsageTypeID = dbo.OEMSailList_Tbl.UsageID)) AS UsageType,
                          (SELECT     Construction
                            FROM          dbo.Construction_Tbl
                            WHERE      (ConstructionID = dbo.OEMSailList_Tbl.ConstructionID)) AS Construction,
                          (SELECT     PartCode
                            FROM          dbo.Fabric_Tbl
                            WHERE      (FabricID = dbo.OEMSailList_Tbl.FabricID)) AS Fabric,
                          (SELECT     ISNULL(DiscountEURO, 0) AS Expr1
                            FROM          dbo.Fabric_Tbl AS Fabric_Tbl_1
                            WHERE      (FabricID = dbo.OEMSailList_Tbl.FabricID)) AS DiscountEuro,
                          (SELECT     OptionName
                            FROM          dbo.Option_Tbl
                            WHERE      (OptionID = dbo.OEMSailList_Tbl.OptionID)) AS OptionName,
                          (SELECT     CASE WHEN BoatRange IS NULL THEN '' ELSE boatRange END + ' ' + CASE WHEN BoatModel IS NULL 
                                                   THEN '' ELSE boatModel END + ' ' + CASE WHEN BoatType IS NULL THEN '' ELSE boatType END AS Expr1
                            FROM          dbo.GenBoatData_Tbl
                            WHERE      (BoatGenID = dbo.OEMSailList_Tbl.BoatGenID)) AS BoatDescription,
                          (SELECT     CASE WHEN
                                                       (SELECT     SUM(oemoptionlist_tbl.optionid)
                                                         FROM          oemoptionlist_Tbl
                                                         WHERE      oemoptionlist_Tbl.oemsaillistid = oemsaillist_Tbl.oemsaillistID) <>
                                                       (SELECT     SUM(quotelineitemoption_tbl.optionid)
                                                         FROM          quotelineitemoption_Tbl
                                                         WHERE      quotelineitemoption_Tbl.quotelineitemid = oemsaillist_Tbl.quotelineitemid) THEN 1 ELSE 0 END AS Expr1) 
                      AS optionintegrity
FROM         dbo.OEMSailList_Tbl
WHERE     (Standard = 1)